﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.IO;
using SES.CMS.DO;
using SES.CMS.BL;
using System.Data;
using System.Text;

namespace SES.CMS.AdminCP.PageUC
{
    public partial class ucProductUpload : System.Web.UI.UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string FileName = "product_upload.xlsx";
            string SaveLocation = string.Format("{0}\\{1}", Server.MapPath("~/Admincp/xlsfile/"), FileName);
            if (File.Exists(SaveLocation))
            {
                File.Delete(SaveLocation);
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            //Xâu Kiểm tra xem có đúng là file excel không...   
            string exelContentType = "application/vnd.ms-excel";
            string exel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            // Tên file
            string FileName = "product_upload.xlsx";
            // Có file không?
            if (!fuProduct.HasFile)
            {
                lblError.Text = "Vui lòng chọn file";
            }
            else
            {
                try
                {
                    // Kiểm tra định dạng file excel???
                    if (fuProduct.PostedFile.ContentType != exelContentType && fuProduct.PostedFile.ContentType != exel2010ContentType)
                    {
                        lblError.Text = "Vui lòng chọn tệp tin excel!";
                    }
                    else
                    {
                        // Lưu file
                        string SaveLocation = string.Format("{0}\\{1}", Server.MapPath("~/Upload/"), FileName);
                        if (File.Exists(SaveLocation))
                        {
                            File.Delete(SaveLocation);
                        }
                        fuProduct.SaveAs(SaveLocation);
                        lblError.Text = "Tải lên thành công";
                        // Mở kết nối tới file excel
                        string excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", SaveLocation);
                        OleDbConnection connection = new OleDbConnection();
                        connection.ConnectionString = excelConnectionString;
                        // Câu lệnh lấy các dòng dữ liệu từ [Sheet1], muốn lấy từ sheet2 thì thay thế "Sheet1$" => "Sheet2$"
                        string sqlString = string.Format("SELECT [MaHang],[TenHang],[MaLoaiSanPham],[MaDonViTinh],[MaXuatXu],[DonGia] FROM [{0}]", "Sheet1$");
                        OleDbCommand command = new OleDbCommand(sqlString, connection);
                        connection.Open();
                        // Các hàm gọi dữ liệu
                        OleDbDataReader odr = command.ExecuteReader();
                        cmsSanPhamDO objSanPham = new cmsSanPhamDO();
                        cmsSanPhamBL sanPhamBL = new cmsSanPhamBL();
                        // Đọc cho tới hết
                        while (odr.Read())
                        {
                            objSanPham.MaSanPham = validToString(odr,0);
                            objSanPham = sanPhamBL.SelectByMaSanPham(objSanPham);
                            objSanPham.TenSanPham = validToString(odr,1);
                            objSanPham.LoaiSanPhamID = new cmsLoaiSanPhamBL().SelectByMaLoaiSanPham(new cmsLoaiSanPhamDO { MaLoaiSanPham = validToString(odr, 2) }).LoaiSanPhamID;
                            objSanPham.DonViID = new cmsDonViBL().SelectByMaDonViTinh(new cmsDonViDO { MaDonViTinh = validToString(odr, 3) }).DonViID;
                            objSanPham.XuatXuID = new cmsXuatXuBL().SelectByMaXuatXu(new cmsXuatXuDO { MaXuatXu = validToString(odr, 4) }).XuatXuID;
                            objSanPham.DonGia = validToInt64(odr,5);
                        //    objSanPham.HinhAnh = validToString(odr,6);
                       //     objSanPham.Mota = validToString(odr,7);
                       //     objSanPham.MoTaChiTiet = validToString(odr,8);
                            sanPhamBL.InsertOrUpdate(objSanPham);
                        }
                        connection.Close();
                        File.Delete(SaveLocation);

                        Functions.Alert("Dữ liệu đã được đưa lên thành công!", "Default.aspx?Page=ListProduct");
                    }
                }
                catch(Exception ee)
                {
                    lblError.Text = "Lỗi: " + ee.Message.ToString();
                }

            }
            //DataSet dsDVT = new DataSet();
            //dsDVT = ReadXls(fuProduct).Copy();
            //DataTable dtExcel = dsDVT.Tables[0].Copy();
            //cmsDonViDO objDonVi = new cmsDonViDO();
            //cmsDonViBL donViBL = new cmsDonViBL();
            //foreach (DataRow dr in dtExcel.Rows)
            //{
            //    objDonVi.MaDonViTinh = dr["Ma Don Vi"].ToString();
            //    objDonVi.DonViTinh = dr["Ten Don Vi"].ToString();
            //    objDonVi.MoTa = dr["Mo Ta"].ToString();

            //    donViBL.InsertOrUpdateDonVi(objDonVi);
            //}
        }
        protected string validToString(OleDbDataReader myreader, int stval)//if any columns are 
        //found null then they are replaced by zero
        {
            object val = myreader[stval];
            if (val != DBNull.Value)
                return val.ToString().Trim();
            else
                return "";
        }
        protected Int64 validToInt64(OleDbDataReader myreader, int stval)//if any columns are 
        //found null then they are replaced by zero
        {
            object val = myreader[stval];
            if (val != DBNull.Value)
            {
                Int64 number;
                bool isNumber = Int64.TryParse(val.ToString().Replace(",", "").Trim(), out number);
                if (isNumber)
                {
                    return number;
                }
                else
                    return 0;
            }
            else
                return 0;
        }
        public DataSet ReadXls(FileUpload fu)
        {
            string SaveLocation = string.Format("{0}\\{1}", Server.MapPath("~/AdminCP/xlsfile/"), fu.FileName);
            fu.SaveAs(SaveLocation);

            DataSet ds = new DataSet();
            string connectStr = new StringBuilder().Append(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=")
                .Append(SaveLocation)
                .Append(";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'").ToString();


            OleDbConnection con = new OleDbConnection(connectStr);


            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [DVT$]", con);

            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            try
            {
                con.Open();
                adapter.Fill(ds, "DVT");
                con.Close();
                File.Delete(SaveLocation);
                return ds;

            }
            catch (Exception ex)
            {

            }
            return ds;

        }
    }
}